# -*- coding: utf-8 -*-
# @Time    : 2020/9/14 12:25
# @Author  : liuwenfan
# @File    : group_service.py
# @Software: PyCharm

import time
from datetime import datetime, timedelta

from sqlalchemy import func, desc
from sqlalchemy.orm import aliased

from application import db
from application.libs import const, framework, ymdhms_to_timestamp
from application.libs.enum import status_enum, priority_enum, severity_enum, project_group_enum, \
    project_leader_mail_enum
from application.libs.send_msg import email_send_html
from application.models.mantis_models import MantisBugTable, MantisProjectTable, MantisCategoryTable, MantisUserTable, \
    MantisCustomFieldStringTable, MantisBugTagTable, MantisTagTable


def bug_by_days(project_id, version_info, type):
    """
    项目bug根据天数趋势图
    :param project_id: 项目id
    :param version_info: 版本
    :param type: 类型  hour/day
    :return: list
    """
    # select FROM_UNIXTIME(date_submitted,'%Y-%m-%d')  as a ,count(1) as b from mantis_bug_table where project_id = '25'
    # and version = 'v7.0.2' GROUP BY a ORDER BY a ASC
    t_format = '%Y-%m-%d'
    if type == 'hour':
        t_format = '%Y-%m-%d %H'
    result = db.session.query(func.FROM_UNIXTIME(MantisBugTable.date_submitted, t_format).label("submit_date"),
                              func.count(1).label("bug_count")).filter(MantisBugTable.project_id == project_id,
                                                                       MantisBugTable.version == version_info).group_by(
        "submit_date").order_by("submit_date").all()

    ret = []

    if result:
        data_start = result[0].submit_date

        for item in result:
            while item.submit_date != data_start:
                ret.append({"value": 0, "date": data_start})
                if type == 'hour':
                    data_start = framework.date_format_h(data_start, '%Y-%m-%d %H', 1)
                else:
                    data_start = framework.date_format_ymd(data_start, '%Y-%m-%d', 1)
            item_dict = {
                "value": item.bug_count,
                "date": item.submit_date
            }
            if type == 'hour':
                data_start = framework.date_format_h(data_start, '%Y-%m-%d %H', 1)
            else:
                data_start = framework.date_format_ymd(data_start, '%Y-%m-%d', 1)
            ret.append(item_dict)

    bn_list = [
        {
            "color": "#67C23A",
            "counter": ret,
            "name": "项目bug趋势图",
            "type": const.BUILD_COUNT_LINE
        }
    ]

    return bn_list


def bug_not_closed(data):
    """
    统计对应人员bug未解决数量及列表，一键发送邮件告知单人，一键发送邮件告知主管，一键发送邮件给项目组
    查询条件为开发权限，且过滤KS-,bug状态非已关闭、已解决
    :param data: 开发人员id，分页参数
    :return: list
    """
    # select a.* from mantis_bug_table a LEFT JOIN mantis_project_table b on a.project_id = b.id where b.`status` = 10
    # and a.`status` = '90' and a.handler_id = '51'
    # 拼接查询条件
    parm = [MantisBugTable.status != 90, MantisBugTable.status != 80]  # 不包含已解决和已关闭
    if data['handler_id']:
        parm.append(MantisBugTable.handler_id == data['handler_id'])
    mantisUser_alias1 = aliased(MantisUserTable, name='user1')
    MantisBugTag_sub = db.session.query(func.MAX(MantisBugTagTable.date_attached).label("date_attached"),
                                        MantisBugTagTable.bug_id.label("bug_id"),
                                        MantisBugTagTable.tag_id.label("tag_id")).filter(
        *parm).group_by(MantisBugTagTable.bug_id).subquery()
    page_data = db.session.query(MantisBugTable, MantisCategoryTable.name.label("category_name"),
                                 MantisUserTable.realname.label("user_realname"),
                                 mantisUser_alias1.realname.label("handler_realname"),
                                 mantisUser_alias1.id.label("handler_id"),
                                 mantisUser_alias1.email.label("email"),
                                 MantisCustomFieldStringTable.value.label("possible_value"),
                                 MantisProjectTable.name.label("project_name"),
                                 MantisTagTable.name.label("tag_name")).outerjoin(
        MantisProjectTable,
        MantisBugTable.project_id == MantisProjectTable.id).outerjoin(
        MantisCategoryTable, MantisCategoryTable.id == MantisBugTable.category_id).outerjoin(
        MantisUserTable, MantisUserTable.id == MantisBugTable.reporter_id).outerjoin(
        mantisUser_alias1, mantisUser_alias1.id == MantisBugTable.handler_id).outerjoin(MantisCustomFieldStringTable,
                                                                                        MantisCustomFieldStringTable.bug_id == MantisBugTable.id).outerjoin(
        MantisBugTag_sub, MantisBugTag_sub.c.bug_id == MantisBugTable.id).outerjoin(
        MantisTagTable, MantisTagTable.id == MantisBugTag_sub.c.tag_id).filter(*parm,
                                                                               mantisUser_alias1.access_level == 40,
                                                                               mantisUser_alias1.realname.notlike(
                                                                                   'KS-%')).order_by(
        desc(MantisBugTable.handler_id)).paginate(page=data[const.PAGE_NUM],
                                                  per_page=data[const.PAGE_PER], error_out=False)
    response_dic = {}
    response_list = []
    if page_data.items:
        for msg in page_data.items:
            response_dic['id'] = msg.MantisBugTable.id  # bug编号 7位不够补0
            response_dic['version'] = msg.MantisBugTable.version  # 产品版本
            response_dic['summary'] = msg.MantisBugTable.summary  # 摘要
            response_dic['status'] = status_enum(
                msg.MantisBugTable.status).name  # 状态 '10:新建,30:重新打开,40:已确认,50:已分配,80:已解决,90:已关闭';
            response_dic['platform'] = msg.MantisBugTable.platform  # 平台
            response_dic['priority'] = priority_enum(msg.MantisBugTable.priority).name  # 优先级 '20:低,30:中,40:高,50:紧急'
            response_dic['severity'] = severity_enum(
                msg.MantisBugTable.severity).name  # 严重性 '10:建议,50:较小错误,60:一般错误,70:严重错误,80:致命错误'
            response_dic['category_name'] = msg.category_name  # 分类
            response_dic['date_submitted'] = time.strftime("%Y-%m-%d %H:%M:%S",
                                                           time.localtime(msg.MantisBugTable.date_submitted))  # 报告日期
            response_dic['last_updated'] = time.strftime("%Y-%m-%d %H:%M:%S",
                                                         time.localtime(msg.MantisBugTable.last_updated))  # 最后更新
            response_dic['user_realname'] = msg.user_realname  # 报告员
            response_dic['handler_realname'] = msg.handler_realname  # 处理人
            response_dic['handler_id'] = msg.handler_id  # 处理人id,页面不显示
            response_dic['email'] = msg.email  # 处理人id,页面不显示
            response_dic['possible_value'] = msg.possible_value  # 上报类型
            response_dic['project_name'] = msg.project_name  # 项目
            response_dic['tag_name'] = msg.tag_name  # 标签
            response_list.append(response_dic.copy())
            response_dic.clear()
    response_dic['items'] = response_list
    response_dic['total'] = page_data.total
    return response_dic


def bug_time_finish(data):
    """
    以项目为维度，统计提出bug至解决bug时间
    :param data: 开发人员id，分页参数
    :return: list
    """
    response_dic, response_list = {}, []
    take_times = 0
    if data["project_id"] == '' or data["version"] == '':
        response_dic['items'] = []
        response_dic['total'] = 0
        response_dic['average_time'] = 0  # 花费时间的平均值
    else:
        page_data = db.session.query(
            func.FROM_UNIXTIME(MantisBugTable.date_submitted, "%Y-%m-%d %H:%i:%s").label("submit_date"),
            func.FROM_UNIXTIME(MantisBugTable.last_updated, "%Y-%m-%d %H:%i:%s").label("close_date"),
            (MantisBugTable.last_updated - MantisBugTable.date_submitted).label(
                "take_time"), MantisUserTable.realname.label("user_realname"),
            MantisBugTable.id, MantisBugTable.summary, MantisBugTable.status).outerjoin(
            MantisUserTable, MantisUserTable.id == MantisBugTable.handler_id).filter(
            MantisBugTable.status == 90, MantisBugTable.project_id == data["project_id"],
            MantisBugTable.version == data["version"]).order_by(desc("submit_date")).paginate(page=data[const.PAGE_NUM],
                                                                                              per_page=data[
                                                                                                  const.PAGE_PER],
                                                                                              error_out=False)
        if page_data.items:
            for msg in page_data.items:
                response_dic['id'] = msg.id  # bug编号 7位不够补0
                response_dic['summary'] = msg.summary  # 摘要
                response_dic['status'] = status_enum(msg.status).name  # 状态 '10:新建,30:重新打开,40:已确认,50:已分配,80:已解决,90:已关闭';
                response_dic['date_submitted'] = msg.submit_date  # 报告日期
                response_dic['last_updated'] = msg.close_date  # 最后更新
                response_dic['take_time'] = framework.timestamp_to_hms(msg.take_time)
                take_times += msg.take_time  # 花费时间，页面显示，非数据库字段
                response_dic['user_realname'] = msg.user_realname  # 开发人员
                response_list.append(response_dic.copy())
                response_dic.clear()
        response_dic['items'] = response_list
        response_dic['total'] = len(response_list)
        if len(response_list) == 0:
            response_dic['average_time'] = 0
        else:
            response_dic['average_time'] = framework.timestamp_to_hms(take_times / len(response_list))  # 花费时间的平均值
    return response_dic


def bug_of_handler(data):
    """
    人名或项目组、时间，可展示出对应人员的bug数柱状图、线上bug情况柱状图
    :return: list
    """
    # SELECT *,count(1) FROM mantis_bug_table where handler_id='52' GROUP BY mantis_bug_table.handler_id
    # 拼接查询条件
    parm = []
    if data['user_id'] != '':
        parm.append(MantisBugTable.handler_id == data['user_id'])
    if not parm:
        if data['project'].strip() != '':
            parm.append(MantisUserTable.realname.like(project_group_enum[data["project"]].value + '%'))
    # if data['start_time'] != '':
    #     parm.append(MantisBugTable.date_submitted >= data['start_time'])
    # if data['end_time'] != '':
    #     parm.append(MantisBugTable.date_submitted <= data['end_time'])
    if data['start_time'] != '':
        data['start_time'] += ' 00:00:00'
        parm.append(MantisBugTable.last_updated >= ymdhms_to_timestamp(data['start_time']))
    if data['end_time'] != '':
        data['end_time'] += ' 23:59:59'
        parm.append(MantisBugTable.last_updated <= ymdhms_to_timestamp(data['end_time']))
    result = db.session.query(MantisUserTable.realname.label("realname"),
                              func.count(MantisBugTable.id).label("bug_count")).outerjoin(
        MantisUserTable, MantisBugTable.handler_id == MantisUserTable.id).filter(*parm).group_by(
        MantisBugTable.handler_id).order_by(desc("realname")).all()
    result_online = db.session.query(MantisUserTable.realname.label("realname"),
                                     func.count(MantisBugTable.id).label("online_bug_count")).outerjoin(
        MantisBugTable, MantisBugTable.handler_id == MantisUserTable.id).filter(MantisBugTable.status != 90,
                                                                                MantisBugTable.project_id == 45,
                                                                                *parm).group_by(
        MantisBugTable.handler_id).order_by(desc("realname")).all()
    result_online_dic = {k[0]: k[1] for k in result_online}
    ret, bn_list = [], []
    if result:
        ret2 = []
        item_dict = {}
        for item in result:
            item_dict["value"] = item.bug_count
            item_dict["date"] = item.realname
            ret.append(item_dict.copy())
            item_dict.clear()
            item_dict["value"] = validate_key(result_online_dic, item.realname)  # 线上bug数
            item_dict["date"] = item.realname
            ret2.append(item_dict.copy())
            item_dict.clear()
        bn_list = [{
            "color": "#67C23A",
            "counter": ret.copy(),
            "name": const.BUG_SORT_Y,
            "type": const.TOTAL_CASE_BAR
        },
            {
                "color": "#67C23A",
                "counter": ret2,
                "name": const.BUG_SORT_Y,
                "type": const.TOTAL_CASE_BAR
            }
        ]
        ret.clear()
    result = db.session.query(MantisBugTable.status.label("status"),
                              func.count(MantisBugTable.project_id).label("status_bug_count")).outerjoin(
        MantisUserTable, MantisBugTable.handler_id == MantisUserTable.id).filter(*parm).group_by(
        MantisBugTable.status).order_by(desc("status_bug_count")).all()
    if result:
        for item in result:
            item_dict = {
                "value": item.status_bug_count,
                "date": status_enum(item.status).name
            }
            ret.append(item_dict)
        bn_list.append(
            {
                "color": "#67C23A",
                "counter": ret,
                "name": const.BUG_SORT_Y,
                "type": const.TOTAL_CASE_BAR
            }
        )

    return bn_list


def validate_key(result_online_dic, realname):
    """
    验证词典中是否存在key值，否则取0
    :param result_online_dic: 线上bug数和人名字典
    :param realname: 人名
    :return:int
    """
    if realname not in result_online_dic.keys():
        return 0
    else:
        return result_online_dic[realname]


def bug_of_project(data):
    """
    bug情况
    :param data: 时间
    :return:json
    """
    parm = [MantisProjectTable.status == 10,MantisBugTable.resolution != 60, MantisBugTable.resolution != 70, MantisBugTable.resolution != 90]  # 目前在维护的项目
    if data['start_time'] != '':
        data['start_time'] += ' 00:00:00'
        parm.append(MantisBugTable.date_submitted >= ymdhms_to_timestamp(data['start_time']))
    if data['end_time'] != '':
        data['end_time'] += ' 23:59:59'
        parm.append(MantisBugTable.date_submitted <= ymdhms_to_timestamp(data['end_time']))
    result = db.session.query(MantisProjectTable.name.label("name"),
                              func.count(MantisBugTable.id).label("bug_count")).outerjoin(MantisProjectTable,
                                                                                          MantisBugTable.project_id == MantisProjectTable.id).filter(
        *parm).group_by(
        MantisProjectTable.id).order_by(desc("bug_count")).all()
    ret = []
    result_n = 0
    if result:
        for item in result:
            item_dict = {
                "value": item.bug_count,
                "date": item.name
            }
            result_n += item.bug_count
            ret.append(item_dict)
    bn_list = [
        {
            "color": "#67C23A",
            "counter": ret.copy(),
            "name": const.BUG_SORT_Y,
            "type": const.TOTAL_CASE_BAR,
            "result": result_n
        }
    ]
    return bn_list


def this_monday(t):
    """
    :function: 获取本周周一日期
    :param t:当前时间戳
    :return: 返回周一的日期时间戳
    :return_type: string
    """
    today = datetime.strptime(str(time.strftime("%Y-%m-%d", time.localtime(t))), "%Y-%m-%d")
    # 先转换为时间数组
    timeArray = time.strptime(datetime.strftime(today - timedelta(today.weekday()), '%Y-%m-%d %H:%M:%S'),
                              "%Y-%m-%d %H:%M:%S")
    # 转换为时间戳
    timeStamp = int(time.mktime(timeArray))
    return timeStamp


def bug_data_last_week():
    """
    上周bug严重级别分布、上周bug平台分布、上周bug类型分布、上周bug处理情况
    :return:json
    """
    t = time.time()  # 获取当前时间戳
    parm = [MantisProjectTable.status == 10, MantisBugTable.date_submitted >= this_monday(t),
            MantisBugTable.date_submitted <= t]  # 目前在维护的项目
    # parm = [MantisProjectTable.status == 10, MantisBugTable.date_submitted >= 1540349477,
    #         MantisBugTable.date_submitted <= 1640349477]  # 目前在维护的项目
    res_list, ret = {}, []
    n_platform_str, n_bug, n_severity, n_category, n_status, n_platform = '', 0, 0, 0, 0, 0
    result_bug = db.session.query(MantisBugTable.id).filter(MantisBugTable.date_submitted >= this_monday(t),
                                                            MantisBugTable.date_submitted <= t).all()
    if result_bug:
        n_bug = len(result_bug)
    result_severity = db.session.query(MantisBugTable.severity.label("severity"),
                                       func.count(MantisBugTable.id).label("bug_count")).outerjoin(MantisProjectTable,
                                                                                                   MantisProjectTable.id == MantisBugTable.project_id).filter(
        *parm).group_by(MantisBugTable.severity).order_by(desc("bug_count")).all()
    if result_severity:
        for item in result_severity:
            item_dict = {
                "value": item.bug_count,
                "name": severity_enum(item.severity).name + ' ' + str(item.bug_count)
            }
            n_severity += item.bug_count
            ret.append(item_dict)
    res_list["severity"] = ret.copy()
    ret.clear()
    result_platform = db.session.query(MantisBugTable.platform.label("platform"), MantisBugTable.id.label("id"),
                                       func.count(MantisBugTable.id).label("bug_count")).outerjoin(MantisProjectTable,
                                                                                                   MantisProjectTable.id == MantisBugTable.project_id).filter(
        *parm).group_by(MantisBugTable.platform).order_by(desc("bug_count")).all()
    if result_platform:
        for item in result_platform:
            item_dict = {
                "value": item.bug_count,
                "name": item.platform + ' ' + str(item.bug_count)
            }
            n_platform += item.bug_count
            if not item.platform:
                n_platform_str += str(item.id) + ';'
            ret.append(item_dict)
    res_list["platform"] = ret.copy()
    ret.clear()
    result_category = db.session.query(MantisCategoryTable.name.label("category"),
                                       func.count(MantisBugTable.id).label("bug_count")).outerjoin(MantisBugTable,
                                                                                                   MantisBugTable.category_id == MantisCategoryTable.id).outerjoin(
        MantisProjectTable,
        MantisProjectTable.id == MantisBugTable.project_id).filter(
        *parm).group_by(MantisCategoryTable.name).order_by(desc("bug_count")).all()
    if result_category:
        for item in result_category:
            item_dict = {
                "value": item.bug_count,
                "name": item.category + ' ' + str(item.bug_count)
            }
            n_category += item.bug_count
            ret.append(item_dict)
    res_list["category"] = ret.copy()
    ret.clear()
    result_status = db.session.query(MantisBugTable.status.label("status"),
                                     func.count(MantisBugTable.project_id).label("bug_count")).outerjoin(
        MantisUserTable, MantisBugTable.handler_id == MantisUserTable.id).outerjoin(MantisProjectTable,
                                                                                    MantisProjectTable.id == MantisBugTable.project_id).filter(
        *parm).group_by(
        MantisBugTable.status).order_by(desc("bug_count")).all()
    if result_status:
        for item in result_status:
            item_dict = {
                "value": item.bug_count,
                "name": status_enum(item.status).name + ' ' + str(item.bug_count)
            }
            n_status += item.bug_count
            ret.append(item_dict)
    res_list["status"] = ret.copy()
    if n_platform_str == '':
        res_list[
            "result"] = f"各饼图数据正确，上周bug数量总计{str(n_bug)},bug严重级别合计{str(n_severity)},bug平台分布合计{str(n_platform)},bug类型分布合计{str(n_category)},bug处理情况合计{str(n_status)}。"
    else:
        res_list["result"] = "bug平台分布饼图数据有误，bug编号:" + n_platform_str
    return res_list


def bug_e_mail(data):
    """
    一键发送邮件告知单人，一键发送邮件告知主管，一键发送邮件给项目组
    :param data: 主送收件人数组
    :return: json
    """
    if not isinstance(data, list):
        raise TypeError
    if data:
        msg = const.OPERATE_FAIL
        new_data = []
        login_user_email = ''
        login_user = db.session.query(MantisUserTable).filter(
            MantisUserTable.username == data[0]['login_username']).first()
        if login_user:
            login_user_email = login_user.email
        for handler in data:
            if handler not in new_data:  # 人名列表去重
                new_data.append(handler)
        receiver_list = []
        msg = const.OPERATE_SUCCESS
        for handler in new_data:  # 开发、主管
            realname = handler['handler_realname']
            receiver_list.append(handler['email'])
            if realname.startswith(project_group_enum.项目一组.value):
                receiver_list.append(project_leader_mail_enum.项目一组主管.value)
            elif realname.startswith(project_group_enum.项目二组.value):
                receiver_list.append(project_leader_mail_enum.项目二组主管.value)
            elif realname.startswith(project_group_enum.项目三组.value):
                receiver_list.append(project_leader_mail_enum.项目三组主管.value)
            elif realname.startswith(project_group_enum.App研发组.value):
                receiver_list.append(project_leader_mail_enum.App研发组主管.value)
            elif realname.startswith(project_group_enum.终端支持组.value):
                receiver_list.append(project_leader_mail_enum.终端支持组主管.value)
            elif realname.startswith(project_group_enum.前端组.value):
                receiver_list.append(project_leader_mail_enum.前端组主管.value)
            elif realname.startswith(project_group_enum.大数据.value):
                receiver_list.append(project_leader_mail_enum.大数据主管.value)  # 邮件地址拼接
            if login_user_email:
                receiver_list.append(login_user_email)
            email_info = f"""
            <html>
                    <head>
                        <meta charset='utf-8'>
                    </head>
                <body>
                    <p>{realname}名下未解决BUG,Mantis地址如下:</p>
                    <a href="https://mantis.kcimg.cn/search.php?project_id=0&handler_id={handler["handler_id"]}&sort=last_updated&dir=DESC&hide_status=80&match_type=0">转至Mantis,请点击</a>
                </body>
            <html>
                """
            try:
                receiver_list_new = list(set(receiver_list))
                # print('>>>>>>>>>>>>>>>>>>>>>>>>>', receiver_list_new)
                email_send_html(receiver_list_new, f"{realname}名下未解决BUG", email_info)  # 调用发送邮件的方法
                receiver_list.clear()
            except:
                msg = const.OPERATE_FAIL
    else:
        msg = const.OPERATE_FAIL
    return msg
